---
title: "OMC - Data Transformation into a TSCS dataset"
output: html_notebook
---
Now, the aggregated data is transformed into a time-series cross-sectional structure with the number of actions/events per municipality-year as the unit of analysis. 

```{r}
library(tidyverse)
library(readstata13)

omc <- read_csv("Ortega(2024)-OMC-CasosFinal_Aggregated(09142024).csv")

omc_tscs_t <- omc %>%
  group_by(year, divipola_alt, mun_uc) %>%
  summarise(conf_events = n(),
            clashes = sum(clash),
            clashes_2 = sum(clash_2),
            air_attacks = sum(air_attack),
            mil_operations = sum(mil_operation),
            tatt_mil_targets = sum(tatt_mil_target),
            ambushes = sum(ambush),
            hitnruns = sum(hitnrun),
            raids = sum(raid),
            assassinations = sum(assassination),
            massacres = sum(massacre),
            f_disapps = sum(f_disapp),
            ind_kidns = sum(ind_kidn),
            col_kidns = sum(col_kidn),
            sex_vs = sum(sex_v),
            attack_civps = sum(attack_civp),
            tatt_civ_targets = sum(tatt_civ_target),
            map_muses = sum(map_muse), 
            tatt_amb_targets = sum(tatt_amb_target),
            unil_actions = air_attacks + mil_operations 
            + tatt_mil_targets + ambushes + hitnruns + raids
            + assassinations + massacres + f_disapps + ind_kidns
            + col_kidns + sex_vs + attack_civps + tatt_civ_targets
            + map_muses + tatt_amb_targets) 

#################################################################
  x <- omc_tscs_t %>% # Check that UA are not greater than CE
      group_by(year) %>%
      mutate(check = conf_events - unil_actions,
             log_st = case_when(
        check < 0 ~ "Review")) 
  # Everything is correct. Checked on 09/14/2024.
#################################################################

omc_tscs_t_mt <- omc %>%
  filter(target == "1. Military") %>%
  group_by(year, divipola_alt, mun_uc) %>%
  summarise(conf_events_mt = n())

omc_tscs_t_ct <- omc %>%
  filter(target == "2. Civilian") %>%
  group_by(year, divipola_alt, mun_uc) %>%
  summarise(conf_events_ct = n())
            
omc_tscs_tf <- left_join(omc_tscs_t, omc_tscs_t_mt)
omc_tscs_tf <- left_join(omc_tscs_tf, omc_tscs_t_ct)

omc_tscs_tf <- omc_tscs_tf %>% 
  relocate(divipola_alt, mun_uc, year, conf_events, conf_events_mt, conf_events_ct,
           clashes, clashes_2, unil_actions)

#### The Old Model ####

# State Forces

omc_tscs_sf <- omc %>%
  filter(state_forces == 1) %>%
  group_by(year, divipola_alt, mun_uc) %>%
  summarise(sf_conf_events = n(),
            sf_clashes = sum(clash),
            sf_clashes_2 = sum(clash_2),
            sf_air_attacks = sum(air_attack),
            sf_mil_operations = sum(mil_operation),
            sf_ambushes = sum(ambush),
            sf_hitnruns = sum(hitnrun),
            sf_raids = sum(raid),
            sf_assassinations = sum(assassination),
            sf_massacres = sum(massacre),
            sf_f_disapps = sum(f_disapp),
            sf_ind_kidns = sum(ind_kidn),
            sf_col_kidns = sum(col_kidn),
            sf_sex_vs = sum(sex_v),
            sf_attack_civps = sum(attack_civp),
            sf_map_muses = sum(map_muse), 
            sf_unil_actions = sf_air_attacks + sf_mil_operations 
            + sf_ambushes + sf_hitnruns + sf_raids
            + sf_assassinations + sf_massacres + sf_f_disapps + sf_ind_kidns
            + sf_col_kidns + sf_sex_vs + sf_attack_civps
            + sf_map_muses) 
# I excluded tatt_mil_targets, tatt_civ_targets, and tatt_amb_targets because they are exclusive actions of nonstate armed groups, according to the methodology of OMC.

omc_tscs_sf_mt <- omc %>%
  filter(state_forces == 1) %>%
  filter(target == "1. Military") %>%
  group_by(year, divipola_alt, mun_uc) %>%
  summarise(sf_conf_events_mt = n())

omc_tscs_sf_ct <- omc %>%
  filter(state_forces == 1) %>%
  filter(target == "2. Civilian") %>%
  group_by(year, divipola_alt, mun_uc) %>%
  summarise(sf_conf_events_ct = n())
            
omc_tscs_sf_f <- left_join(omc_tscs_sf, omc_tscs_sf_mt)
omc_tscs_sf_f <- left_join(omc_tscs_sf_f, omc_tscs_sf_ct)

omc_tscs_sf_f <- omc_tscs_sf_f %>% 
  relocate(divipola_alt, mun_uc, year, sf_conf_events, sf_conf_events_mt,
           sf_conf_events_ct, sf_clashes, sf_unil_actions)

# Rebel Groups

omc_tscs_rg <- omc %>%
  filter(rebel_groups == 1) %>%
  group_by(year, divipola_alt, mun_uc) %>%
  summarise(rg_conf_events = n(),
            rg_clashes = sum(clash),
            rg_clashes_2 = sum(clash_2),
            rg_tatt_mil_targets = sum(tatt_mil_target),
            rg_ambushes = sum(ambush),
            rg_hitnruns = sum(hitnrun),
            rg_raids = sum(raid),
            rg_assassinations = sum(assassination),
            rg_massacres = sum(massacre),
            rg_f_disapps = sum(f_disapp),
            rg_ind_kidns = sum(ind_kidn),
            rg_col_kidns = sum(col_kidn),
            rg_sex_vs = sum(sex_v),
            rg_attack_civps = sum(attack_civp),
            rg_tatt_civ_targets = sum(tatt_civ_target),
            rg_map_muses = sum(map_muse), 
            rg_tatt_amb_targets = sum(tatt_amb_target),
            rg_unil_actions = rg_tatt_mil_targets + rg_ambushes + rg_hitnruns + rg_raids
            + rg_assassinations + rg_massacres + rg_f_disapps + rg_ind_kidns
            + rg_col_kidns + rg_sex_vs + rg_attack_civps + rg_tatt_civ_targets
            + rg_map_muses + rg_tatt_amb_targets) 
# I excluded air_attack and mil_operation because they are exclusive actions of the state, according to the methodology of OMC.

omc_tscs_rg_mt <- omc %>%
  filter(rebel_groups == 1) %>%
  filter(target == "1. Military") %>%
  group_by(year, divipola_alt, mun_uc) %>%
  summarise(rg_conf_events_mt = n())

omc_tscs_rg_ct <- omc %>%
  filter(rebel_groups == 1) %>%
  filter(target == "2. Civilian") %>%
  group_by(year, divipola_alt, mun_uc) %>%
  summarise(rg_conf_events_ct = n())
            
omc_tscs_rg_f <- left_join(omc_tscs_rg, omc_tscs_rg_mt)
omc_tscs_rg_f <- left_join(omc_tscs_rg_f, omc_tscs_rg_ct)

omc_tscs_rg_f <- omc_tscs_rg_f %>% 
  relocate(divipola_alt, mun_uc, year, rg_conf_events, rg_conf_events_mt,
           rg_conf_events_ct, rg_clashes, rg_unil_actions)

# Pro-government Militias

omc_tscs_pm <- omc %>%
  filter(progov_militias == 1) %>%
  group_by(year, divipola_alt, mun_uc) %>%
  summarise(pm_conf_events = n(),
            pm_clashes = sum(clash),
            pm_clashes_2 = sum(clash_2),
            pm_tatt_mil_targets = sum(tatt_mil_target),
            pm_ambushes = sum(ambush),
            pm_hitnruns = sum(hitnrun),
            pm_raids = sum(raid),
            pm_assassinations = sum(assassination),
            pm_massacres = sum(massacre),
            pm_f_disapps = sum(f_disapp),
            pm_ind_kidns = sum(ind_kidn),
            pm_col_kidns = sum(col_kidn),
            pm_sex_vs = sum(sex_v),
            pm_attack_civps = sum(attack_civp),
            pm_tatt_civ_targets = sum(tatt_civ_target),
            pm_map_muses = sum(map_muse), 
            pm_tatt_amb_targets = sum(tatt_amb_target),
            pm_unil_actions = pm_tatt_mil_targets + pm_ambushes + pm_hitnruns + pm_raids
            + pm_assassinations + pm_massacres + pm_f_disapps + pm_ind_kidns
            + pm_col_kidns + pm_sex_vs + pm_attack_civps + pm_tatt_civ_targets 
            + pm_map_muses + pm_tatt_amb_targets) 
# I excluded air_attack and mil_operation because they are exclusive actions of the state, according to the methodology of OMC.


omc_tscs_pm_mt <- omc %>%
  filter(progov_militias == 1) %>%
  filter(target == "1. Military") %>%
  group_by(year, divipola_alt, mun_uc) %>%
  summarise(pm_conf_events_mt = n())

omc_tscs_pm_ct <- omc %>%
  filter(progov_militias == 1) %>%
  filter(target == "2. Civilian") %>%
  group_by(year, divipola_alt, mun_uc) %>%
  summarise(pm_conf_events_ct = n())
            
omc_tscs_pm_f <- left_join(omc_tscs_pm, omc_tscs_pm_mt)
omc_tscs_pm_f <- left_join(omc_tscs_pm_f, omc_tscs_pm_ct)

omc_tscs_pm_f <- omc_tscs_pm_f %>% 
  relocate(divipola_alt, mun_uc, year, pm_conf_events, pm_conf_events_mt,
           pm_conf_events_ct, pm_clashes, pm_unil_actions)

  # Clashes state vs. rebel groups

omc_tscs_cl_sfrg <- omc %>%
  filter(state_forces == 1 & rebel_groups == 1) %>%
  group_by(year, divipola_alt, mun_uc) %>%
  summarise(cl_st_rg_1 = sum(clash),
            cl_st_rg_2 = sum(clash_2))

  # Clashes rebel groups vs. pro-government militias

omc_tscs_cl_rgpm <- omc %>%
  filter(rebel_groups == 1 & progov_militias == 1) %>%
  group_by(year, divipola_alt, mun_uc) %>%
  summarise(cl_rg_pm_1a = sum(clash),
            cl_rg_pm_2a = sum(clash_2))

omc_tscs_cl_rgpm_alt <- omc %>%
  filter(rebel_groups == 1 & progov_militias == 1 & state_forces == 0) %>%
  group_by(year, divipola_alt, mun_uc) %>%
  summarise(cl_rg_pm_1b = sum(clash),
            cl_rg_pm_2b = sum(clash_2)) # I think this is better because it excludes the cases in which the participates.

### Aggregate all the final datasets

omc_tscs_ff <- left_join(omc_tscs_tf, omc_tscs_sf_f)
omc_tscs_ff <- left_join(omc_tscs_ff, omc_tscs_rg_f)
omc_tscs_ff <- left_join(omc_tscs_ff, omc_tscs_pm_f)
omc_tscs_ff <- left_join(omc_tscs_ff, omc_tscs_cl_sfrg)
omc_tscs_ff <- left_join(omc_tscs_ff, omc_tscs_cl_rgpm)
omc_tscs_ff <- left_join(omc_tscs_ff, omc_tscs_cl_rgpm_alt)

omc_tscs_ff <- omc_tscs_ff %>% replace(is.na(.), 0) # It is correct because the NAs are created for the aggregation of data.

write_csv(omc_tscs_ff, "Ortega(2024)-OMC-TSCS_CasosFinal-Updated(09142024).csv")
write_xlsx(omc_tscs_ff, "Ortega(2024)-OMC-TSCS_CasosFinal-Updated(09142024).xlsx")

```

End of the old version of the dataset.

```{r}

omc_cmt <- read_csv("Ortega(2024)-OMC-CasosFinal_MilitaryTarget-Updated(09132024).csv")

str(omc_cmt)

omc_tscs_t_st_rg <- omc_cmt %>%
  group_by(year, divipola_alt, mun_uc) %>%
  summarise(clashes_st_rg = sum(cl_st_rg),
            clashes_2_st_rg = sum(cl_st_rg_2),
            air_attacks_sxr = sum(sf_air_attacks_xr),
            mil_operations_sxr = sum(sf_mil_operations_xr),
            ambushes_sxr = sum(sf_ambushes_xr),
            hitnruns_sxr = sum(sf_hitnruns_xr),
            raids_sxr = sum(sf_raids_xr),
            tatt_mil_targets_rxs = sum(rg_tatt_mil_targets_xs),
            ambushes_rxs = sum(rg_ambushes_xs),
            hitnruns_rxs = sum(rg_hitnruns_xs),
            raids_rxs = sum(rg_raids_xs)) 

write_csv(omc_tscs_t_st_rg, "Ortega(2024)-OMC-TSCS_State_Rebels-Updated(09142024).csv")
  
```

